﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;
using System.Data.SqlClient;
//using _WebShared.Classes;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class JobSiteDAL : IJobSiteDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public JobSiteDAL()
        { 
        
        }

        public DataTable ListJobSite(string strJobSiteTypeSeq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@job_site_type_seq", strJobSiteTypeSeq);

                strSQL = "select j.job_site_seq, j.job_site_ref, j.job_site_name"
                    + " from hrm_mp_job_sites j"
                    + " where record_status not like @DeleteRecord"
                    + (!string.IsNullOrEmpty(strJobSiteTypeSeq) ? " and j.job_site_type_seq = @job_site_type_seq" : "")
                    + " order by j.job_site_name";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
       
        public IList<JobSiteInfo> ListJobSite(JobSiteInfo jsInfo)
        {
            string strSQL;
            int JobSiteTypes = 0;
            int UpperSeq = 0;
            int JobSiteLevel = 0;

            if (jsInfo.JobSiteTypes != null)
                JobSiteTypes = jsInfo.JobSiteTypes.Sequence;
            if (jsInfo.Upper != null)
                UpperSeq = (int) jsInfo.Upper;
            if (jsInfo.JobSiteLevels != null)
                JobSiteLevel = jsInfo.JobSiteLevels.Sequence;

            List<JobSiteInfo> infoList = new List<JobSiteInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@Upper", "%/" + UpperSeq.ToString() + "/%");
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpperSeq", UpperSeq);
                dbManager.AddParameters(3, "@Seq", jsInfo.Sequence);
                dbManager.AddParameters(4, "@JobSiteTypes", JobSiteTypes);
                dbManager.AddParameters(5, "@JobSiteLevel", JobSiteLevel);

                strSQL = "SELECT j.JOB_SITE_SEQ, j.JOB_SITE_REF, j.JOB_SITE_NAME"
                        + ", j.JOB_SITE_NAME_SHORT, j.JOB_SITE_NAME_ENG, j.JOB_SITE_NAME_ENG_SHORT"
                        + ", j.JOB_SITE_FULL_PATH, j.gfmis_code, j.JOB_DESCRIPTION, j.REMARKS"
                        + ", j.CREATED_BY, j.UPDATED_BY"    //, j.DATE_START, j.DATE_END
                        + ", j.ADDRESS, j.POSTAL_CODE, j.TEL, j.FAX, j.upper_job_site_seq"
                        + ", j.JOB_SITE_TYPE_SEQ, j.WORK_AREA_SEQ, j.JOB_SITE_LEVEL_SEQ"
                        + ", j.DISTRICT_SEQ, j.CITY_SEQ, j.COUNTRY_SEQ, j.RECORD_STATUS, rs.RECORD_STATUS_NAME"
                        + ", j.ADDRESS2, j.WEBADDRESS, j. head_pos_seq"
                    + " FROM HRM_MP_JOB_SITES j"
                        + " LEFT JOIN MAS_RECORD_STATUSES rs ON j.RECORD_STATUS = rs.RECORD_STATUS"
                    + " WHERE j.RECORD_STATUS not like @DeleteRecord"
                    + (JobSiteLevel > 0 ? " AND j.JOB_SITE_LEVEL_SEQ <= @JobSiteLevel" : "")
                    + (jsInfo.Sequence > 0 ? " AND j.JOB_SITE_SEQ = @Seq" 
                        : " AND j.JOB_SITE_FULL_PATH LIKE "
                            + (UpperSeq > 0 ? " @Upper"
                                : " (select concat('%/', curr_job_site_seq, '/%') from hrm_mp_job_site_types "
                                        + " where job_site_type_seq = @JobSiteTypes)")
                            //+ " AND j.JOB_SITE_SEQ not like @JobSiteSeq" // ไม่เอาตัวเอง
                        )
                    + (JobSiteTypes > 0 ? " and job_site_type_seq = @JobSiteTypes" : "")
                    + " ORDER BY j.JOB_SITE_REF + 0";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    JobSiteTypeDAL typeDAL = new JobSiteTypeDAL();
                    JobSiteTypeInfo typeInfo = new JobSiteTypeInfo();
                    if (!rdr.IsDBNull(17))
                    {
                        IList<JobSiteTypeInfo> typeList = typeDAL.ListJobSiteTypes((rdr.IsDBNull(17) ? (int?)null : rdr.GetInt32(17)));
                        if (typeList.Count > 0)
                        {
                            typeInfo = typeList[0];
                        }
                    }

                    WorkAreaDAL areaDAL = new WorkAreaDAL();
                    WorkAreaInfo areaInfo = new WorkAreaInfo();
                    if (!rdr.IsDBNull(18))
                    {
                        IList<WorkAreaInfo> areaList = areaDAL.ListWorkAreas((rdr.IsDBNull(18) ? (int?)null : rdr.GetInt32(18)));
                        if (areaList.Count > 0)
                        {
                            areaInfo = areaList[0];
                        }
                    }

                    JobsiteLevelDAL levelDAL = new JobsiteLevelDAL();
                    JobSiteLevelInfo levelInfo = new JobSiteLevelInfo();
                    if (!rdr.IsDBNull(19))
                    {
                        IList<JobSiteLevelInfo> levelList = levelDAL.ListJobSiteLevels((rdr.IsDBNull(19) ? 0 : rdr.GetInt32(19)));
                        if (levelList.Count > 0)
                        {
                            levelInfo = levelList[0];
                        }
                    }

                    MasDistrictDAL disDAL = new MasDistrictDAL();
                    MasDistrictInfo disInfo = new MasDistrictInfo();
                    if (!rdr.IsDBNull(20))
                    {
                        IList<MasDistrictInfo> disList = disDAL.ListDistricts(new MasDistrictInfo(rdr.IsDBNull(20) ? 0 : rdr.GetInt32(20)));
                        if (disList.Count > 0)
                        {
                            disInfo = disList[0];
                        }
                    }

                    MasCityDAL cityDAL = new MasCityDAL();
                    MasCityInfo cityInfo = new MasCityInfo();
                    if (!rdr.IsDBNull(21))
                    {
                        IList<MasCityInfo> cityList = cityDAL.ListMasCities(new MasCityInfo(rdr.IsDBNull(21) ? 0 : rdr.GetInt32(21)));
                        if (cityList.Count > 0)
                        {
                            cityInfo = cityList[0];
                        }
                    }

                    MasCountryInfo countryInfo = new MasCountryInfo();
                    MasCountryDAL countryDAL = new MasCountryDAL();
                    if (!rdr.IsDBNull(22))
                    {
                        IList<MasCountryInfo> countryList = countryDAL.ListMasCountries(new MasCountryInfo(rdr.IsDBNull(22) ? 0 : rdr.GetInt32(22)));
                        if (countryList.Count > 0)
                        {
                            countryInfo = countryList[0];
                        }
                    }

                    JobSiteInfo Info = new JobSiteInfo(rdr.IsDBNull(0) ? (int?)null : rdr.GetInt32(0)
                        , rdr.IsDBNull(1) ? "" : rdr.GetString(1)
                        , rdr.IsDBNull(2) ? "" : rdr.GetString(2)
                        , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                        , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                        , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                        , rdr.IsDBNull(6) ? "" : rdr.GetString(6)
                        , rdr.IsDBNull(7) ? "" : rdr.GetString(7)
                        , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                        , rdr.IsDBNull(9) ? "" : rdr.GetString(9)
                        , rdr.IsDBNull(10) ? (int?)null : rdr.GetInt32(10)
                        , rdr.IsDBNull(11) ? (int?)null : rdr.GetInt32(11)
                        //, rdr.IsDBNull(12) ? (DateTime?)null : rdr.GetDateTime(12)
                        //, rdr.IsDBNull(13) ? (DateTime?)null : rdr.GetDateTime(13)
                        , new AddressInfo(rdr.IsDBNull(12) ? "" : rdr.GetString(12)
                            , rdr.IsDBNull(13) ? "" : rdr.GetString(13)
                            , rdr.IsDBNull(14) ? "" : rdr.GetString(14)
                            , rdr.IsDBNull(15) ? "" : rdr.GetString(15)
                            , ""
                            , "")
                        , disInfo
                        , cityInfo
                        , countryInfo
                        , rdr.IsDBNull(16) ? (int?)null : rdr.GetInt32(16)
                        , typeInfo
                        , areaInfo
                        , levelInfo
                        , rdr.IsDBNull(25) ? null : rdr.GetString(25)
                        , rdr.IsDBNull(26) ? null : rdr.GetString(26)
                        , rdr.IsDBNull(27) ? (int?)null : rdr.GetInt32(27)
                        );

                    infoList.Add(Info);
                }
                
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

         //   dbManager.Dispose();
            return infoList;
        }

        public IList<JobSiteInfo> LoadJobSiteTreeView(Nullable<int> JobSiteSeq, Nullable<int> JobSiteTypeSeq)
		{
            string strSQL;
			/*string TypeName = JobSiteTypeSeq == 1 ? MODEL.Caption.Get("TreeView โครงสร้างตามกฎกระทรวงโหนด Root")
				: MODEL.Caption.Get("TreeView โครงสร้างตามการบริหารจัดการภายในโหนด Root");*/
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<JobSiteInfo> infoList = new List<JobSiteInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@JobSiteSeq", JobSiteSeq);
                dbManager.AddParameters(1, "@JobSiteTypeSeq", JobSiteTypeSeq);
                dbManager.AddParameters(2, "@JobSiteUpper", "%/" + JobSiteSeq.ToString() + "/%");
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@CancelRecord", SystemInfo._CancelRecordStatus);
              //  dbManager.AddParameters(5, "@TypeName", TypeName);

                strSQL = "SELECT t.* FROM("
                    + " SELECT 0 AS JOB_SITE_SEQ"
                        + ", JOB_SITE_TYPE_NAME AS JOB_SITE_NAME"
                        + ", NULL AS upper_job_site_seq"
                        + ", NULL AS JOB_SITE_REF"
                    + " FROM HRM_MP_JOB_SITE_TYPES"
                    + " WHERE RECORD_STATUS NOT IN(@DeleteRecord, @CancelRecord) "
                    + " AND JOB_SITE_TYPE_SEQ = @JobSiteTypeSeq"
                    + " UNION ALL "
                    + "SELECT j.JOB_SITE_SEQ, j.JOB_SITE_NAME"
                    //+ ", CASE WHEN j.JOB_SITE_SEQ = @JobSiteSeq"
                    + ", CASE WHEN j.JOB_SITE_SEQ =(select curr_job_site_seq from hrm_mp_job_site_types "
                                        + " where job_site_type_seq = @JobSiteTypeSeq)"
                        + " THEN 0"
                        + " ELSE "
                            + " CASE WHEN j.upper_job_site_seq IS NULL THEN 0"
                                + " ELSE j.upper_job_site_seq"
                            + " END"
                        + " END AS upper_job_site_seq"
                    + ", j.JOB_SITE_REF"
                    + " FROM HRM_MP_JOB_SITES j"
                    //+ " LEFT JOIN HRM_MP_JOB_SITE_TYPES jt ON j.JOB_SITE_TYPE_SEQ = jt.JOB_SITE_TYPE_SEQ"
                    + " WHERE j.RECORD_STATUS NOT IN (@DeleteRecord, @CancelRecord)"
                    + " AND j.JOB_SITE_FULL_PATH LIKE " 
                        + (JobSiteSeq.HasValue ? " @JobSiteUpper"
                            : " (select concat('%/', curr_job_site_seq, '/%') from hrm_mp_job_site_types "
                                        + " where job_site_type_seq = @JobSiteTypeSeq)")
                        + " AND j.JOB_SITE_TYPE_SEQ = @JobSiteTypeSeq"
                    + ")t"
                    + " ORDER BY t.JOB_SITE_REF + 0";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        JobSiteInfo Info = new JobSiteInfo();

                        Info.Sequence = Convert.IsDBNull(dr[i]["JOB_SITE_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["JOB_SITE_SEQ"].ToString());
                        Info.Name = Convert.IsDBNull(dr[i]["JOB_SITE_NAME"]) ? "" : (dr[i]["JOB_SITE_NAME"]).ToString();
                        Info.Upper = Convert.IsDBNull(dr[i]["upper_job_site_seq"]) ? (int?)null : Int32.Parse(dr[i]["upper_job_site_seq"].ToString());
                        
                        infoList.Add(Info);
                    }
                }
                return infoList;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public DataSet LoadJobSites(string JobSiteType)
        //{
        //}

        public DataSet LoadJobSites(JobSiteInfo jsInfo)
        {
            string strSQL;            
            int JobSiteTypes = 0;
            int UpperSeq = 0;
            int JobSiteLevel = 0;

            if (jsInfo.JobSiteTypes != null)
                JobSiteTypes = jsInfo.JobSiteTypes.Sequence;
            if (jsInfo.Upper != null)
                UpperSeq = (int)jsInfo.Upper;
            if (jsInfo.JobSiteLevels != null)
                JobSiteLevel = jsInfo.JobSiteLevels.Sequence;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@Upper", String.Format("%/{0}/%", UpperSeq));
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@UpperSeq", UpperSeq);
                dbManager.AddParameters(3, "@Seq", jsInfo.Sequence);
                dbManager.AddParameters(4, "@JobSiteTypes", JobSiteTypes);
                dbManager.AddParameters(5, "@JobSiteLevel", JobSiteLevel);
                //dbManager.AddParameters(6, "@SearchText", String.IsNullOrEmpty(jsInfo.SearchText) ? String.Empty : jsInfo.SearchText);
                dbManager.AddParameters(6, "@SearchText", jsInfo.SearchText == null ? null : jsInfo.SearchText);

                strSQL = "SELECT j.JOB_SITE_SEQ, j.JOB_SITE_REF, j.JOB_SITE_NAME"
                        + ", j.JOB_SITE_NAME_SHORT, j.JOB_SITE_NAME_ENG, j.JOB_SITE_NAME_ENG_SHORT"
                        + ", j.upper_job_site_seq, ju.JOB_SITE_NAME AS UPPER_JOB_SITE_NAME"
                        + ", j.ref_job_site_seq, ex.JOB_SITE_NAME AS EXT_JOB_SITE_NAME"
                        + ", j.JOB_SITE_FULL_PATH, j.WORK_AREA_SEQ, j.gfmis_code"
                        + ", j.JOB_SITE_LEVEL_SEQ, j.JOB_DESCRIPTION, j.ADDRESS, j.DISTRICT_SEQ"
                        + ", j.POSTAL_CODE, j.TEL, j.FAX, j.REMARKS"   // invalid column name: j.DATE_START, j.DATE_END 
                        + ", ju.JOB_SITE_NAME AS JOB_SITE_UPPER_NAME, a.WORK_AREA_NAME"
                        + ", CONCAT(dis.DISTRICT_NAME, ' ', am.AMP_NAME) AS DISTRICT_AMP"
                        + ", j.CITY_SEQ, ci.CITY_NAME"
                        + ", j.COUNTRY_SEQ, c.COUNTRY_NAME, j.RECORD_STATUS, rs.RECORD_STATUS_NAME"
                        + ", dis.AMP_SEQ, am.PROV_SEQ, l.JOB_SITE_LEVEL_NAME, z.ZONE_NAME"
                        // Remark : hrm_mp_job_sites.head_pos_seq = ID ตำแหน่ง (pos_seq) หัวหน้าสำนักหรือหน่วยงานใด (เฉพาะโครงสร้างภายใน - Type = 2)
						+ ", j.ADDRESS2, j.WEBADDRESS, j.head_pos_seq, po.POS_NAME  "

                    + " FROM HRM_MP_JOB_SITES j"
                        + " LEFT JOIN HRM_MP_JOB_SITES ju ON j.upper_job_site_seq = ju.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES ex ON j.ref_job_site_seq = ex.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_WORK_AREAS a ON j.WORK_AREA_SEQ = a.WORK_AREA_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITE_LEVELS l ON j.JOB_SITE_LEVEL_SEQ = l.JOB_SITE_LEVEL_SEQ"
                        + " LEFT JOIN MAS_DISTRICTS dis ON j.DISTRICT_SEQ = dis.DISTRICT_SEQ"
                        + " LEFT JOIN MAS_AMPHURS am ON dis.AMP_SEQ = am.AMP_SEQ"
                        + " LEFT JOIN MAS_PROVINCES pro ON am.PROV_SEQ = pro.PROV_SEQ"
                        + " LEFT JOIN MAS_COUNTRIES c ON j.COUNTRY_SEQ = c.COUNTRY_SEQ"
                        + " LEFT JOIN MAS_CITIES ci ON j.COUNTRY_SEQ = ci.COUNTRY_SEQ"
                        + " LEFT JOIN MAS_COUNTRY_ZONES z ON c.ZONE_SEQ = z.ZONE_SEQ"
                        + " INNER JOIN MAS_RECORD_STATUSES rs ON j.RECORD_STATUS = rs.RECORD_STATUS"
                        + " LEFT JOIN hrm_mp_positions po on po.pos_seq = j.head_pos_seq"
                         
                    + " WHERE j.RECORD_STATUS = @ActiveRecord"
                    //+ " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                    //    + " ELSE (j.JOB_SITE_NAME LIKE CONCAT('%', @SearchText , '%')"
                    //        + ") END"
                    + " AND (@SearchText = '' OR j.JOB_SITE_NAME LIKE CONCAT('%', @SearchText , '%'))  " 

                    + (JobSiteLevel > 0 ? " AND j.JOB_SITE_LEVEL_SEQ <= @JobSiteLevel" : "")
                    + (jsInfo.Sequence > 0 ? " AND j.JOB_SITE_SEQ = @Seq"
                        : " AND j.JOB_SITE_FULL_PATH LIKE "
                            + (UpperSeq > 0 ? " @Upper"
                                : " (select concat('%/', curr_job_site_seq, '/%') from hrm_mp_job_site_types "
                                        + " where job_site_type_seq = @JobSiteTypes)")
                    //+ " AND j.JOB_SITE_SEQ not like @JobSiteSeq" // ไม่เอาตัวเอง
                     )
                    + " ORDER BY j.JOB_SITE_REF";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch(Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }

        //public DataTable SelectGrid(string upper_job_site_seq, string is_internal)
        //{
        //    string strSQL;
        //    DataTable dtb = null;

        //    try
        //    {
        //        dbManager.Open();
        //        //dbManager.AddParameters(0, "@upper_job_site_seq", upper_job_site_seq);

        //        strSQL = "SELECT j.JOB_SITE_SEQ, j.JOB_SITE_REF, j.JOB_SITE_NAME"
        //            + ", j.JOB_SITE_NAME_SHORT, j.JOB_SITE_NAME_ENG, j.upper_job_site_seq"
        //            + ", j.JOB_SITE_FULL_PATH, j.WORK_AREA_SEQ, j.gfmis_code"
        //            + ", j.JOB_SITE_LEVEL_SEQ, j.JOB_DESCRIPTION, j.ADDRESS, j.DISTRICT_SEQ"
        //            + ", j.POSTAL_CODE, j.DATE_START, j.DATE_END, j.REMARKS, j.RECORD_STATUS"
        //            + ", ju.JOB_SITE_NAME AS JOB_SITE_UPPER_NAME"
        //            + ", a.WORK_AREA_NAME, a.WORK_AREA_ID + ' - ' + a.WORK_AREA_NAME AS WORK_AREA_TEXT"
        //            + ", dis.DISTRICT_NAME + ' ' + am.AMP_NAME + ' ' + pro.PROV_NAME AS DISTRICT_TEXT"
        //            + ", j.COUNTRY_SEQ, c.COUNTRY_NAME"
        //            + ", j.DATE_START, j.DATE_END"
        //            + ", j.RECORD_STATUS, rs.RECORD_STATUS_NAME"
        //            + " FROM HRM_MP_JOB_SITES j"
        //            + " LEFT JOIN HRM_MP_JOB_SITES ju ON j.upper_job_site_seq = ju.JOB_SITE_SEQ"
        //            + " LEFT JOIN HRM_MP_WORK_AREAS a ON j.WORK_AREA_SEQ = a.WORK_AREA_SEQ"
        //            + " LEFT JOIN MAS_DISTRICTS dis ON j.DISTRICT_SEQ = dis.DISTRICT_SEQ"
        //            + " LEFT JOIN MAS_AMPHURS am ON dis.AMP_SEQ = am.AMP_SEQ"
        //            + " LEFT JOIN MAS_PROVINCES pro ON am.PROV_SEQ = pro.PROV_SEQ"
        //            + " LEFT JOIN MAS_COUNTRIES c ON j.COUNTRY_SEQ = c.COUNTRY_SEQ"
        //            + " INNER JOIN MAS_RECORD_STATUSES rs ON j.RECORD_STATUS = rs.RECORD_STATUS"
        //            + " WHERE j.RECORD_STATUS not like 'D'"
        //            + (upper_job_site_seq.Length > 0 ? " AND j.upper_job_site_seq = " + Misc.ValueForSQL(upper_job_site_seq, Misc.DATA_TYPE_HINT.NUMERIC) : "")
        //            + (is_internal.Length > 0 ? " AND j.IS_INTERNAL = " + Misc.ValueForSQL(is_internal) : "");

        //        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return dtb;
        //}

        //public DataTable SelectTreeView(string job_site_seq, string is_internal)
        //{
        //    string strSQL;
        //    DataTable dtb = null;

        //    try
        //    {
        //        dbManager.Open();
        //        //dbManager.AddParameters(0, "@upper_job_site_seq", "%/" + job_site_seq.ToString() + "%/");

        //        strSQL = "SELECT j.JOB_SITE_SEQ, j.JOB_SITE_NAME"
        //            + ", CASE WHEN j.JOB_SITE_SEQ = " + Misc.ValueForSQL(job_site_seq, Misc.DATA_TYPE_HINT.NUMERIC)
        //                + " THEN null"
        //                + " ELSE j.upper_job_site_seq"
        //                + " END AS upper_job_site_seq"
        //            + " FROM HRM_MP_JOB_SITES j"
        //            + " WHERE j.RECORD_STATUS NOT IN ('C', 'D')"
        //            + (job_site_seq.Length > 0 ? " AND j.JOB_SITE_FULL_PATH LIKE " + Misc.ValueForSQL("%/" + job_site_seq + "/%") : "")
        //            + (is_internal.Length > 0 ? " AND j.IS_INTERNAL = " + Misc.ValueForSQL(is_internal) : "");

        //        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return dtb;
        //}

        //public DataTable SelectDropDownList(string job_site_seq, string is_internal)
        //{
        //    string strSQL;
        //    DataTable dtb = null;

        //    try
        //    {
        //        dbManager.Open();
        //        //dbManager.AddParameters(0, "@upper_job_site_seq", upper_job_site_seq);

        //        strSQL = "SELECT null AS JOB_SITE_SEQ, null AS JOB_SITE_NAME, null AS upper_job_site_seq"
        //            + ", null AS JOB_SITE_REF"

        //            + " UNION ALL"

        //            + " SELECT j.JOB_SITE_SEQ, j.JOB_SITE_NAME, j.upper_job_site_seq, j.JOB_SITE_REF"
        //            + " FROM HRM_MP_JOB_SITES j"
        //            + " WHERE j.RECORD_STATUS NOT IN ('C', 'D')"
        //            + (job_site_seq.Length > 0 ? " AND j.JOB_SITE_FULL_PATH LIKE " + Misc.ValueForSQL("%/" + job_site_seq + "/%") : "")
        //            + (is_internal.Length > 0 ? " AND j.IS_INTERNAL = " + Misc.ValueForSQL(is_internal) : "")
        //            + " ORDER BY JOB_SITE_REF";

        //        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return dtb;
        //}

        //public DataRow SelectRow(string job_site_seq)
        //{
        //    string strSQL;
        //    DataRow dr = null;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "SELECT j.JOB_SITE_SEQ, j.JOB_SITE_REF, j.JOB_SITE_NAME"
        //            + ", j.JOB_SITE_NAME_SHORT, j.JOB_SITE_NAME_ENG, j.upper_job_site_seq"
        //            + ", j.JOB_SITE_FULL_PATH, j.WORK_AREA_SEQ, j.gfmis_code"
        //            + ", j.JOB_SITE_LEVEL_SEQ, j.JOB_DESCRIPTION, j.ADDRESS, j.DISTRICT_SEQ"
        //            + ", j.POSTAL_CODE, j.DATE_START, j.DATE_END, j.REMARKS, j.RECORD_STATUS"
        //            + ", ju.JOB_SITE_NAME AS JOB_SITE_UPPER_NAME"
        //            + ", a.WORK_AREA_NAME, a.WORK_AREA_ID + ' - ' + a.WORK_AREA_NAME AS WORK_AREA_TEXT"
        //            + ", dis.DISTRICT_NAME + ' ' + am.AMP_NAME + ' ' + pro.PROV_NAME AS DISTRICT_TEXT"
        //            + ", j.COUNTRY_SEQ, c.COUNTRY_NAME"
        //            + ", j.DATE_START, j.DATE_END"
        //            + ", j.RECORD_STATUS, rs.RECORD_STATUS_NAME"
        //            + " FROM HRM_MP_JOB_SITES j"
        //            + " LEFT JOIN HRM_MP_JOB_SITES ju ON j.upper_job_site_seq = ju.JOB_SITE_SEQ"
        //            + " LEFT JOIN HRM_MP_WORK_AREAS a ON j.WORK_AREA_SEQ = a.WORK_AREA_SEQ"
        //            + " LEFT JOIN MAS_DISTRICTS dis ON j.DISTRICT_SEQ = dis.DISTRICT_SEQ"
        //            + " LEFT JOIN MAS_AMPHURS am ON dis.AMP_SEQ = am.AMP_SEQ"
        //            + " LEFT JOIN MAS_PROVINCES pro ON am.PROV_SEQ = pro.PROV_SEQ"
        //            + " LEFT JOIN MAS_COUNTRIES c ON j.COUNTRY_SEQ = c.COUNTRY_SEQ"
        //            + " INNER JOIN MAS_RECORD_STATUSES rs ON j.RECORD_STATUS = rs.RECORD_STATUS"
        //            + " WHERE j.JOB_SITE_SEQ = " + Misc.ValueForSQL(job_site_seq, Misc.DATA_TYPE_HINT.NUMERIC);

        //        dr = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0];
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return dr;
        //}

        public void Insert(JobSiteInfo Info)
        {
            string strSQL;

            Nullable<int> TypeSeq = Info.JobSiteTypes == null ? null : (Nullable<int>)Info.JobSiteTypes.Sequence;
            Nullable<int> LevelSeq = Info.JobSiteLevels == null ? null : (Nullable<int>) Info.JobSiteLevels.Sequence;
            Nullable<int> workareaSeq = Info.WorkAreas == null ? null : (Nullable<int>)Info.WorkAreas.Sequence;
            Nullable<int> districtSeq = Info.Districts == null ? null : (Nullable<int>)Info.Districts.Sequence;
            Nullable<int> countrySeq = Info.Countries == null ? null : (Nullable<int>)Info.Countries.Sequence;
            Nullable<int> citySeq = Info.City == null ? null : (Nullable<int>)Info.City.Sequence;
            Nullable<int> JobSiteExternal = Info.External == null ? null : (Nullable<int>)Info.External;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(28);

                dbManager.AddParameters(0, "@JobSiteRef", Info.RefNo);
                dbManager.AddParameters(1, "@JobSiteName", Info.Name);
                dbManager.AddParameters(2, "@JobSiteNameShort", Info.NameShort);
                dbManager.AddParameters(3, "@JobSiteNameEng", Info.NameEng);
                dbManager.AddParameters(4, "@UpperSeq", Info.Upper);
                dbManager.AddParameters(5, "@FullPath", Info.FullPath);
                dbManager.AddParameters(6, "@JobSiteTypeSeq", TypeSeq > 0 ? TypeSeq : null);
                dbManager.AddParameters(7, "@WorkAreaSeq", workareaSeq > 0 ? workareaSeq : null);
                dbManager.AddParameters(8, "@GFMIS", Info.GFMIS);
                dbManager.AddParameters(9, "@JobSiteLevel", LevelSeq > 0 ? LevelSeq : null);
                dbManager.AddParameters(10, "@JobDescription", Info.JobDescription);
                dbManager.AddParameters(11, "@Address", Info.Address.Address);
                dbManager.AddParameters(12, "@DistrictSeq", districtSeq > 0 ? districtSeq : null);
                dbManager.AddParameters(13, "@CitySeq", citySeq > 0 ? citySeq : null);
                dbManager.AddParameters(14, "@CountrySeq", countrySeq > 0 ? countrySeq : null);
                dbManager.AddParameters(15, "@PostalCode", Info.Address.PostalCode);
                //dbManager.AddParameters(16, "@StartDate", Info.StartDate);
                //dbManager.AddParameters(17, "@EndDate", Info.EndDate);
                dbManager.AddParameters(16, "@JobSiteNameEngShort", Info.NameEngShort);
                dbManager.AddParameters(17, "@ProvinceSeq", Info.Address.Province);//Info.Address.Districts.Amphurs.Provinces.Sequence);
                dbManager.AddParameters(18, "@Tel", Info.Address.Phone);
                dbManager.AddParameters(19, "@Fax", Info.Address.Fax);
                dbManager.AddParameters(20, "@JobSiteExternal", JobSiteExternal);
                dbManager.AddParameters(21, "@Remarks", Info.Remarks);
                dbManager.AddParameters(22, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(23, "@CreatedBy", Info.CreatedBy);
				dbManager.AddParameters(24, "@CreatedWhen", DateTime.Today);
                dbManager.AddParameters(25, "@Address2", Info.Address2);
                dbManager.AddParameters(26, "@WebAddress", Info.WebAddress);
                dbManager.AddParameters(27, "@JobSiteHeadSeq", Info.HeadPosSeq);

                strSQL = "INSERT INTO HRM_MP_JOB_SITES"
                 + "(JOB_SITE_REF, ref_job_site_seq, JOB_SITE_NAME, JOB_SITE_NAME_SHORT, JOB_SITE_NAME_ENG, JOB_SITE_NAME_ENG_SHORT"
                     + ", upper_job_site_seq, JOB_SITE_TYPE_SEQ, WORK_AREA_SEQ, gfmis_code, JOB_SITE_LEVEL_SEQ"
                     + ", JOB_DESCRIPTION, ADDRESS, DISTRICT_SEQ, CITY_SEQ, COUNTRY_SEQ, POSTAL_CODE, provnce_seq"
                     + ", TEL, FAX, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN, Address2, WebAddress"  //DATE_START, DATE_END, 
                     + ", head_pos_seq)"
                 + " VALUES (@JobSiteRef, @JobSiteExternal, @JobSiteName, @JobSiteNameShort, @JobSiteNameEng, @JobSiteNameEngShort"
                     + ", @UpperSeq, @JobSiteTypeSeq, @WorkAreaSeq, @GFMIS, @JobSiteLevel, @JobDescription"
                     + ", @Address, @DistrictSeq, @CitySeq, @CountrySeq, @PostalCode, @ProvinceSeq, @Tel, @Fax"
                     + ", @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen, @Address2, @WebAddress" //@StartDate, @EndDate, 
                     + ", @JobSiteHeadSeq)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();

                strSQL = "UPDATE HRM_MP_JOB_SITES"
                        + " SET	JOB_SITE_FULL_PATH = dbo.F_Get_jobsite_fullpath(JOB_SITE_SEQ)"
                        + " WHERE JOB_SITE_FULL_PATH IS NULL"
                            + " AND JOB_SITE_TYPE_SEQ = @JobSiteTypeSeq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
           
       public void Update(JobSiteInfo Info)
        {
            string strSQL;
            Nullable<int> TypeSeq = Info.JobSiteTypes == null ? null : (Nullable<int>)Info.JobSiteTypes.Sequence;
            Nullable<int> LevelSeq = Info.JobSiteLevels == null ? null : (Nullable<int>)Info.JobSiteLevels.Sequence;
            Nullable<int> workareaSeq = Info.WorkAreas == null ? null : (Nullable<int>)Info.WorkAreas.Sequence;
            Nullable<int> districtSeq = Info.Districts == null ? null : (Nullable<int>)Info.Districts.Sequence;
            Nullable<int> countrySeq = Info.Countries == null ? null : (Nullable<int>)Info.Countries.Sequence;
            Nullable<int> citySeq = Info.City == null ? null : (Nullable<int>)Info.City.Sequence;
            Nullable<int> JobSiteExternal = Info.External == null ? null : (Nullable<int>)Info.External;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(28);

                dbManager.AddParameters(0, "@JobSiteRef", Info.RefNo);
                dbManager.AddParameters(1, "@JobSiteName", Info.Name);
                dbManager.AddParameters(2, "@JobSiteNameShort", Info.NameShort);
                dbManager.AddParameters(3, "@JobSiteNameEng", Info.NameEng);
                dbManager.AddParameters(4, "@UpperSeq", Info.Upper);
                dbManager.AddParameters(5, "@FullPath", Info.FullPath);
                dbManager.AddParameters(6, "@JobSiteTypeSeq", TypeSeq > 0 ? TypeSeq : null);
                dbManager.AddParameters(7, "@WorkAreaSeq", workareaSeq > 0 ? workareaSeq : null);
                dbManager.AddParameters(8, "@GFMIS", Info.GFMIS);
                dbManager.AddParameters(9, "@JobSiteLevel", LevelSeq > 0 ? LevelSeq : null);
                dbManager.AddParameters(10, "@JobDescription", Info.JobDescription);
                dbManager.AddParameters(11, "@Address", Info.Address.Address);
                dbManager.AddParameters(12, "@DistrictSeq", districtSeq > 0 ? districtSeq : null);
                dbManager.AddParameters(13, "@CitySeq", citySeq > 0 ? citySeq : null);
                dbManager.AddParameters(14, "@CountrySeq", countrySeq > 0 ? countrySeq : null);
                dbManager.AddParameters(15, "@PostalCode", Info.Address.PostalCode);
                //dbManager.AddParameters(16, "@StartDate", Info.StartDate);
                //dbManager.AddParameters(17, "@EndDate", Info.EndDate);
                dbManager.AddParameters(16, "@JobSiteNameEngShort", Info.NameEngShort);
                dbManager.AddParameters(17, "@ProvinceSeq", null);//Info.Districts.Amphurs.Provinces.Sequence);
                dbManager.AddParameters(18, "@Tel", Info.Address.Phone);
                dbManager.AddParameters(19, "@Fax", Info.Address.Fax);
                dbManager.AddParameters(20, "@Remarks", Info.Remarks);
                dbManager.AddParameters(21, "@JobSiteExternal", JobSiteExternal);
				dbManager.AddParameters(22, "@Sequence", Info.Sequence);
				dbManager.AddParameters(23, "@UpdatedBy", Info.UpdatedBy);
				dbManager.AddParameters(24, "@UpdatedWhen", DateTime.Today);
				dbManager.AddParameters(25, "@Address2", Info.Address2);
                dbManager.AddParameters(26, "@WebAddress", Info.WebAddress);
                dbManager.AddParameters(27, "@JobSiteHeadSeq", Info.HeadPosSeq);

                strSQL = "UPDATE HRM_MP_JOB_SITES"
                + " SET JOB_SITE_REF = @JobSiteRef"
                    + ", ref_job_site_seq = @JobSiteExternal"
                    + ", JOB_SITE_NAME = @JobSiteName"
                    + ", JOB_SITE_NAME_SHORT = @JobSiteNameShort"
                    + ", JOB_SITE_NAME_ENG = @JobSiteNameEng"
                    + ", JOB_SITE_NAME_ENG_SHORT = @JobSiteNameEngShort"
                    + ", upper_job_site_seq = @UpperSeq"
                    //+ ", JOB_SITE_FULL_PATH = @FullPath"
                    + ", JOB_SITE_TYPE_SEQ = @JobSiteTypeSeq"
                    + ", WORK_AREA_SEQ = @WorkAreaSeq"
                    + ", gfmis_code = @GFMIS"
                    + ", JOB_SITE_LEVEL_SEQ = @JobSiteLevel"
                    + ", JOB_DESCRIPTION = @JobDescription"
                    + ", ADDRESS = @Address"
                    + ", DISTRICT_SEQ = @DistrictSeq"
                    + ", CITY_SEQ = @CitySeq"
                    + ", COUNTRY_SEQ = @CountrySeq"
                    + ", POSTAL_CODE = @PostalCode"
                    + ", provnce_seq = @ProvinceSeq"
                    + ", TEL = @Tel"
                    + ", FAX = @Fax"
                    //+ ", DATE_START = @StartDate"
                    //+ ", DATE_END = @EndDate"
					+ ", REMARKS = @Remarks"
					+ ", UPDATED_BY = @UpdatedBy"
					+ ", UPDATED_WHEN = @UpdatedWhen"
                    + ", Address2 = @Address2"
                    + ", WebAddress = @WebAddress"
                    + ", head_pos_seq = @JobSiteHeadSeq"
                + " WHERE JOB_SITE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();

                if (Info.Upper != null)
                {
                    strSQL = "UPDATE HRM_MP_JOB_SITES"
                        + " SET	JOB_SITE_FULL_PATH = F_Get_jobsite_fullpath(JOB_SITE_SEQ)"
                        + " WHERE JOB_SITE_SEQ = @Sequence";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                    dbManager.CommitTransaction();
                }
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

       public void Delete(int Sequence, int UpdatedBy)
       {
           string strSQL;

           try
           {
               dbManager.Open();
               dbManager.BeginTransaction();
               dbManager.CreateParameters(4);

               dbManager.AddParameters(0, "@seq", Sequence);
               dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
               dbManager.AddParameters(2, "@updated_by", UpdatedBy);
               dbManager.AddParameters(3, "@updated_when", DateTime.Today);

               strSQL = "UPDATE HRM_MP_JOB_SITES"
                   + " SET RECORD_STATUS = @record_status"
                       + ", UPDATED_BY = @updated_by"
                       + ", UPDATED_WHEN = @updated_when"
                   + " WHERE JOB_SITE_SEQ = @seq";

               dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
               dbManager.CommitTransaction();
           }
           catch
           {
               dbManager.RollBackTransaction();
               throw;
           }
           finally
           {
               dbManager.Dispose();
           }
       }
       //public JobSiteInfo GetInfo(int JOB_SITE_SEQ)
       //{
       //    JobSiteInfo info = new JobSiteInfo();
       //    DataRowCollection drc = null;

       //    try
       //    {
       //        dbManager.Open();
       //        dbManager.CreateParameters(3);

       //        dbManager.AddParameters(0, "@JOB_SITE_SEQ", JOB_SITE_SEQ);
       //        dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
       //        dbManager.AddParameters(2, "@CancelRecord", SystemInfo._CancelRecordStatus);

       //        string strSQL;

       //        strSQL = "SELECT JOB_SITE_SEQ, upper_job_site_seq"
       //            + " FROM HRM_MP_JOB_SITES"
       //            + " WHERE j.RECORD_STATUS NOT IN (@DeleteRecord, @CancelRecord)"
       //            + " AND j.JOB_SITE_SEQ = @JOB_SITE_SEQ";
       //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

       //        if (drc.Count > 0)
       //        {
       //            info.Sequence = Convert.IsDBNull(drc[0]["JOB_SITE_SEQ"]) ? (int?)null : Int32.Parse(drc[0]["JOB_SITE_SEQ"].ToString());
       //            info.Upper = Convert.IsDBNull(drc[0]["upper_job_site_seq"]) ? (int?)null : Int32.Parse(drc[0]["upper_job_site_seq"].ToString());
       //        }
       //    }
       //    catch (Exception ex)
       //    {
       //        throw (ex);
       //    }
       //    finally
       //    {
       //        dbManager.Dispose();
       //    }

       //    return info;
       //}
       //public void GetJobsiteFullPath(int job_site_seq)
       //{
       //    string full_path = "";
       //    Nullable<int> upper;
       //    JobSiteInfo info = GetInfo(job_site_seq);

       //    upper = info.Upper;

       //    if (upper != null)
       //    {
       //        full_path = Recursive(upper, "/");
       //    }
       //    else
       //    {
       //        full_path = "/" + info.Sequence.ToString() + "/";
       //    }
       //    return full_path;
       //}
       //public void Recursive(int job_site_seq, string full_path)
       //{
       //    Nullable<int> upper;
       //    JobSiteInfo info = GetInfo(job_site_seq);

       //    upper = info.Upper;
       //    full_path = "/" + info.Sequence.ToString();

       //    if (upper != null)
       //    {
       //        full_path = Recursive(upper, full_path);
       //    }

       //    return full_path;
       //}

    }
}
